﻿using System;
using System.Data;
using System.Text;
using System.Web.UI.WebControls;

using HsTec.Mobile.Green.Util;

public partial class DataStorage_DataExport : System.Web.UI.Page
{
    protected int colNum = 4;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string sYear = this.Request.QueryString["sYear"].ToString().Trim();
            string sEnd = this.Request.QueryString["sEnd"].ToString().Trim();
            GetExportList(sYear, sEnd);
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "utf-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=Table6.xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            this.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();
        }
    }

    private void GetExportList(string sYear, string sEnd)
    {
        String sTD = String.Empty;
        String sTitle = String.Empty;
        String sTitle2 = String.Empty;
        String sFormula = String.Empty;
        //1.获取需要的数据
        DataView mydv = SQLHelper.ExecuteDataView("select * from vi_Planning where years>='" + sYear + "' and years<='" + sEnd + "'");
        colNum += mydv.Count;
        //2.填充数值
        DataTable dt = mydv.Table;
        for (int i = 0; i < mydv.Count; i++)
        {
            sTitle2 += "<td style=\"text-align: center\"><strong>实际值</strong></td>";
            if (mydv[i][0].ToString() != DateTime.Now.Year.ToString())
            {
                sTitle += "<td style=\"text-align: center\"><strong>" + mydv[i][0] + "年</strong></td>";
            }
            else
            {
                sTitle += "<td style=\"text-align: center\"><strong>" + DateTime.Now.Year + "年截至" + DateTime.Now.Month + "月</strong></td>";
            }
        }
        lblTableHead.Text = sTitle;
        lblTableHead2.Text = sTitle2;
        for (int i = 1; i < dt.Columns.Count; i++)
        {
            sTD = String.Empty;
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                sTD += "<td>" + mydv[j][i] + "</td>";
            }
            Label lbl = (Label)this.FindControl("Label" + i);
            lbl.Text = sTD;
        }
        for (int i = 61; i < 75; i++)
        {
            sTD = String.Empty;
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                if (j != 0)
                {
                    sTD += "<td>" + GetFormula(i - 60, j + 5) + "</td>";
                }
                else
                {
                    sTD += "<td>&nbsp;</td>";
                }
            }
            Label lblF = (Label)this.FindControl("Label" + i);
            lblF.Text = sTD;
        }
    }

    /// <summary>
    /// 获取公式
    /// </summary>
    /// <param name="i"></param>
    /// <returns></returns>
    private string GetFormula(int iRow, int iNum)
    {
        string sRtn = String.Empty;
        switch (iRow)
        {
            case 1:
                sRtn = "=F5/(F19+F20)*10000".Replace("F", GetRow(iNum));
                break;
            case 2:
                sRtn = "=(F65-E65)/E65".Replace("F", GetRow(iNum)).Replace("E", GetRow(iNum - 1));
                break;
            case 3:
                sRtn = "=F5/F23".Replace("F", GetRow(iNum));
                break;
            case 4:
                sRtn = "=(F67-E67)/E67".Replace("F", GetRow(iNum)).Replace("E", GetRow(iNum - 1));
                break;
            case 5:
                sRtn = "=F5/F21".Replace("F", GetRow(iNum));
                break;
            case 6:
                sRtn = "=(F69-E69)/E69".Replace("F", GetRow(iNum)).Replace("E", GetRow(iNum - 1));
                break;
            case 7:
                sRtn = "=F24/F19".Replace("F", GetRow(iNum));
                break;
            case 8:
                sRtn = "=F61/F60".Replace("F", GetRow(iNum));
                break;
            case 9:
                sRtn = "=F64/F63".Replace("F", GetRow(iNum));
                break;
            case 10:
                sRtn = "=F14/F22*10000".Replace("F", GetRow(iNum));
                break;
            case 11:
                sRtn = "=F7/F19".Replace("F", GetRow(iNum));
                break;
            case 12:
                sRtn = "=F10/F26".Replace("F", GetRow(iNum));
                break;
            case 13:
                sRtn = "=F8/F27".Replace("F", GetRow(iNum));
                break;
            case 14:
                sRtn = "=F11/F28".Replace("F", GetRow(iNum));
                break;
        }
        return sRtn;
    }
    private string GetRow(int iNum)
    {
        StringBuilder sbW = new StringBuilder();
        if (iNum % 26 == 0)
        {
            sbW.Insert(0, 'Z');
            iNum = iNum / 26 - 1;
        }
        else
        {
            sbW.Insert(0, (char)((iNum % 26 - 1) + 'A'));
            iNum /= 26;
        }
        return sbW.ToString();
    }
}
